---
id: diff
slug: /declarative/diff
title: Comparing Schemas
---

import Tabs from '@theme/Tabs';
import TabItem from '@theme/TabItem';

It is sometimes useful to be able to calculate the diff between two
schemas. For instance, as you are developing you may want to calculate
how to move from an existing database to some other state that you are
interested in. Alternatively, you may be diagnosing some issue and want
to verify there is no difference between a local copy of a schema and a
remote one.

To accommodate these types of use-cases, Atlas offers the `schema diff` that accepts two schema states: `--from` and
`--to`, calculates the differences between them, and generates a plan of SQL statements that can be used
to migrate the "from" schema to the state defined by the "to" schema. A state can be specified using a database URL,
an HCL or SQL schema, or a migration directory.

## Flags
* `--from` - a list of [URLs](/concepts/url) to the current state: can be a database URL, an HCL or
SQL schema, or a migration directory.
* `--to` - a list of [URLs](/concepts/url) to the desired state: can be a database URL, an HCL or
SQL schema, or a migration directory.
* `--dev-url` - a [URL](/concepts/url) to the [_Dev-Database_](../concepts/dev.mdx).
* `--schema` (optional, may be supplied multiple times) - schemas to inspect within the target database.
* `--exclude` (optional, may be supplied multiple times) - filter out resources matching the given glob pattern.
* `--format` (optional) - [Go template](https://pkg.go.dev/text/template) to use to format the output.
* `--web` (`-w` accepted as well)- visualize the schema diff as an ERD on [Atlas Cloud](https://auth.atlasgo.cloud/signup). See an
example [here](https://gh.atlasgo.cloud/explore/72b578d2...00799c41).


## Diff Policy

Atlas allows configuring the schema diffing policy in [project configuration](../atlas-schema/projects.mdx) to fine-tune
or modify suggested changes before they are printed:

<Tabs>
<TabItem label="Skip Destructive" value="skip">

```hcl title="atlas.hcl"
variable "destructive" {
  type    = bool
  default = false
}

env "local" {
  diff {
    skip {
      drop_schema = !var.destructive
      drop_table  = !var.destructive
    }
  }
}
```

The usage is as follows:

```go
atlas schema diff --env "local" --var "destructive=true"
```

</TabItem>
<TabItem label="Concurrent Indexes" value="concurrent_indexes">

```hcl title="atlas.hcl"
env "local" {
  diff {
    // By default, indexes are not created or dropped concurrently.
    concurrent_index {
      create = true
      drop   = true
    }
  }
}
```

</TabItem>
</Tabs>

## Examples

### Compare databases

<Tabs defaultValue="mysql">
<TabItem label="MySQL" value="mysql">

```shell
atlas schema diff \
  --from "mysql://user:pass@localhost:3306" \
  --to "mysql://user:pass@remote:3306"
```

</TabItem>
<TabItem label="MariaDB" value="maria">

```shell
atlas schema diff \
  --from "maria://user:pass@localhost:3306" \
  --to "maria://user:pass@remote:3306"
```

</TabItem>
<TabItem label="PostgreSQL" value="postgres">

```shell
atlas schema diff \
  --from "postgres://postgres:pass@localhost:5432/database?sslmode=disable" \
  --to "postgres://postgres:pass@remote:5432/database"
```

</TabItem>
<TabItem label="SQLite" value="sqlite">

```shell
atlas schema diff \
  --from "sqlite://file1.db" \
  --to "sqlite://file2.db"
```

</TabItem>
<TabItem label="SQL Server" value="sqlserver">

```shell
atlas schema diff \
  --from "sqlserver://sa:P@ssw0rd0995@localhost:1433?database=master&mode=database" \
  --to "sqlserver://sa:P@ssw0rd0995@remote:1433?database=master&mode=database"
```

</TabItem>
<TabItem label="ClickHouse" value="clickhouse">

```shell
atlas schema diff \
  --from "docker://clickhouse/23.11" \
  --to "clickhouse://user:pass@remote:9000"
```

</TabItem>
</Tabs>

### Compare database schemas

<Tabs defaultValue="mysql">
<TabItem label="MySQL" value="mysql">

Compare two MySQL [schemas](https://dev.mysql.com/doc/refman/8.0/en/glossary.html#glos_schema)/[databases](https://dev.mysql.com/doc/refman/8.0/en/glossary.html#glos_database)
named `example`:

```shell
atlas schema diff \
  --from "mysql://user:pass@localhost:3306/example" \
  --to "mysql://user:pass@remote:3306/example"
```

</TabItem>
<TabItem label="MariaDB" value="maria">

Compare two MariaDB [schemas](https://dev.mysql.com/doc/refman/8.0/en/glossary.html#glos_schema)/[databases](https://dev.mysql.com/doc/refman/8.0/en/glossary.html#glos_database)
named `example`:

```shell
atlas schema diff \
  --from "maria://user:pass@localhost:3306/example" \
  --to "maria://user:pass@remote:3306/example"
```

</TabItem>
<TabItem label="PostgreSQL" value="postgres">

Compare two PostgreSQL [schemas](https://www.postgresql.org/docs/current/ddl-schemas.html) named `public` under the
`example` database:

```shell
atlas schema diff \
  --from "postgres://postgres:pass@localhost:5432/example?search_path=public&sslmode=disable" \
  --to "postgres://postgres:pass@remote:5432/example?search_path=public"
```

</TabItem>
<TabItem label="SQL Server" value="sqlserver">

Compare two SQL Server schemas:

```shell
atlas schema diff \
  --from "sqlserver://sa:P@ssw0rd0995@localhost:1433?database=master" \
  --to "sqlserver://sa:P@ssw0rd0995@remote:1433?database=master"
```

</TabItem>
<TabItem label="ClickHouse" value="clickhouse">

Compare two ClickHouse schemas/named-databases:

```shell
atlas schema diff \
  --from "clickhouse://user:pass@localhost:9000/example" \
  --to "clickhouse://user:pass@remote:9000/example"
```

</TabItem>
</Tabs>

### Compare HCL schemas

<Tabs defaultValue="mysql">
<TabItem label="MySQL" value="mysql">

```shell
atlas schema diff \
  --from "file://schema1.hcl" \
  --to "file://schema2.hcl" \
  --dev-url "docker://mysql"
```

</TabItem>
<TabItem label="MariaDB" value="maria">

```shell
atlas schema diff \
  --from "file://schema1.hcl" \
  --to "file://schema2.hcl" \
  --dev-url "docker://maria"
```

</TabItem>
<TabItem label="PostgreSQL" value="postgres">

```shell
atlas schema diff \
  --from "file://schema1.hcl" \
  --to "file://schema2.hcl" \
  --dev-url "docker://postgres"
```

</TabItem>
<TabItem label="SQLite" value="sqlite">

```shell
atlas schema diff \
  --from "file://schema1.hcl" \
  --to "file://schema2.hcl" \
  --dev-url "sqlite://file?mode=memory"
```

</TabItem>
<TabItem label="SQL Server" value="sqlserver">

```shell
atlas schema diff \
  --from "file://schema1.hcl" \
  --to "file://schema2.hcl" \
  --dev-url "docker://sqlserver/2022-latest"
```

</TabItem>
<TabItem label="ClickHouse" value="clickhouse">

```shell
atlas schema diff \
  --from "file://schema1.hcl" \
  --to "file://schema2.hcl" \
  --dev-url "docker://clickhouse/23.11"
```

</TabItem>
</Tabs>

### Compare SQL schemas

<Tabs defaultValue="mysql">
<TabItem label="MySQL" value="mysql">

```shell
atlas schema diff \
  --from "file://schema1.sql" \
  --to "file://schema2.sql" \
  --dev-url "docker://mysql/8/test"
```

If the DDL statements only include qualified tables (e.g., `schema`.`table`), you can omit the database name from the
`--dev-url`:

```shell {4}
atlas schema diff \
  --from "file://schema1.sql" \
  --to "file://schema2.sql" \
  --dev-url "docker://mysql"
```

</TabItem>
<TabItem label="MariaDB" value="maria">

```shell
atlas schema diff \
  --from "file://schema1.sql" \
  --to "file://schema2.sql" \
  --dev-url "docker://maria/latest/test"
```

If the DDL statements only include qualified tables (e.g., `schema`.`table`), you can omit the database name from the
`--dev-url`:

```shell {4}
atlas schema diff \
  --from "file://schema1.sql" \
  --to "file://schema2.sql" \
  --dev-url "docker://maria"
```

</TabItem>
<TabItem label="PostgreSQL" value="postgres">

```shell
atlas schema diff \
  --from "file://schema1.sql" \
  --to "file://schema2.sql" \
  --dev-url "docker://postgres"
```

</TabItem>
<TabItem label="SQLite" value="sqlite">

```shell
atlas schema diff \
  --from "file://schema1.sql" \
  --to "file://schema2.sql" \
  --dev-url "sqlite://file?mode=memory"
```

</TabItem>
<TabItem label="SQL Server" value="sqlserver">

```shell
atlas schema diff \
  --from "file://schema1.sql" \
  --to "file://schema2.sql" \
  --dev-url "docker://sqlserver/2022-latest"
```

</TabItem>
<TabItem label="ClickHouse" value="clickhouse">

```shell
atlas schema diff \
  --from "file://schema1.sql" \
  --to "file://schema2.sql" \
  --dev-url "docker://clickhouse/23.11"
```

</TabItem>
</Tabs>


### Compare migration directories

<Tabs defaultValue="mysql">
<TabItem label="MySQL" value="mysql">

```shell
atlas schema diff \
  --from "file://migrations1" \
  --to "file://migrations2" \
  --dev-url "docker://mysql/8/test"
```

If the DDL statements only include qualified tables (e.g., `schema`.`table`), you can omit the database name from the
`--dev-url`:

```shell {4}
atlas schema diff \
  --from "file://migrations1" \
  --to "file://migrations2" \
  --dev-url "docker://mysql"
```

</TabItem>
<TabItem label="MariaDB" value="maria">

```shell
atlas schema diff \
  --from "file://migrations1" \
  --to "file://migrations2" \
  --dev-url "docker://maria/latest/test"
```

If the DDL statements only include qualified tables (e.g., `schema`.`table`), you can omit the database name from the
`--dev-url`:

```shell {4}
atlas schema diff \
  --from "file://migrations1" \
  --to "file://migrations2" \
  --dev-url "docker://maria"
```

</TabItem>
<TabItem label="PostgreSQL" value="postgres">

```shell
atlas schema diff \
  --from "file://migrations1" \
  --to "file://migrations2" \
  --dev-url "docker://postgres"
```

</TabItem>
<TabItem label="SQLite" value="sqlite">

```shell
atlas schema diff \
  --from "file://migrations1" \
  --to "file://migrations2" \
  --dev-url "sqlite://file?mode=memory"
```

</TabItem>
<TabItem label="SQL Server" value="sqlserver">

```shell
atlas schema diff \
  --from "file://migrations1" \
  --to "file://migrations2" \
  --dev-url "docker://sqlserver/2022-latest"
```

</TabItem>
<TabItem label="ClickHouse" value="clickhouse">

```shell
atlas schema diff \
  --from "file://migrations1" \
  --to "file://migrations2" \
  --dev-url "docker://clickhouse/23.11"
```

</TabItem>
</Tabs>

### Compare SQL to HCL

<Tabs defaultValue="mysql">
<TabItem label="MySQL" value="mysql">

```shell
atlas schema diff \
  --from "file://schema.hcl" \
  --to "file://schema.sql" \
  --dev-url "docker://mysql/8/test"
```

</TabItem>
<TabItem label="MariaDB" value="maria">

```shell
atlas schema diff \
  --from "file://schema.hcl" \
  --to "file://schema.sql" \
  --dev-url "docker://maria/latest/test"
```

</TabItem>
<TabItem label="PostgreSQL" value="postgres">

```shell
atlas schema diff \
  --from "file://schema.hcl" \
  --to "file://schema.sql" \
  --dev-url "docker://postgres"
```

</TabItem>
<TabItem label="SQLite" value="sqlite">

```shell
atlas schema diff \
  --from "file://schema.hcl" \
  --to "file://schema.sql" \
  --dev-url "sqlite://file?mode=memory"
```

</TabItem>
<TabItem label="SQL Server" value="sqlserver">

```shell
atlas schema diff \
  --from "file://schema.hcl" \
  --to "file://schema.sql" \
  --dev-url "docker://sqlserver/2022-latest"
```

</TabItem>
<TabItem label="ClickHouse" value="clickhouse">

```shell
atlas schema diff \
  --from "file://schema.hcl" \
  --to "file://schema.sql" \
  --dev-url "docker://clickhouse/23.11"
```

</TabItem>
</Tabs>


### Compare a migration directory to a database

<Tabs defaultValue="mysql">
<TabItem label="MySQL" value="mysql">

```shell
atlas schema diff \
  --from "file://migrations" \
  --to "mysql://root:pass@:3306/example" \
  --dev-url "docker://mysql/8/example"
```

</TabItem>
<TabItem label="MariaDB" value="maria">

```shell
atlas schema diff \
  --from "file://migrations" \
  --to "maria://root:pass@:3306/example" \
  --dev-url "docker://maria/latest/example"
```

</TabItem>
<TabItem label="PostgreSQL" value="postgres">

```shell
atlas schema diff \
  --from "file://migrations" \
  --to "postgres://postgres:pass@localhost:5435/test?sslmode=disable" \
  --dev-url "docker://postgres"
```

</TabItem>
<TabItem label="SQLite" value="sqlite">

```shell
atlas schema diff \
  --from "file://migrations" \
  --to "file://schema.sql" \
  --dev-url "sqlite://file?mode=memory"
```

</TabItem>
<TabItem label="SQL Server" value="sqlserver">

```shell
atlas schema diff \
  --from "file://migrations" \
  --to "sqlserver://sa:P@ssw0rd0995@:1433?database=master" \
  --dev-url "docker://sqlserver/2022-latest"
```

</TabItem>
<TabItem label="ClickHouse" value="clickhouse">

```shell
atlas schema diff \
  --from "file://migrations" \
  --to "clickhouse://user:pass@remote:9000/example" \
  --dev-url "docker://clickhouse/23.11"
```

</TabItem>
</Tabs>

### Compare external schemas

The `schema diff` command can also be used to compare external schemas defined in data sources, such as ORM schemas,
with a database, HCL or SQL schemas, or even with other ORM schemas.

<Tabs defaultValue="mysql">
<TabItem label="MySQL" value="mysql">

```hcl
data "external_schema" "gorm" {
  program = [
    "go", "run", "-mod=mod",
    "gitee.com/damengde/atlas-provider-gorm",
    "load",
    "--path", "./path/to/models",
    "--dialect", "mysql",
  ]
}

data "external_schema" "sequelize" {
  program = [
    "npx",
    "@ariga/atlas-provider-sequelize",
    "load",
    "--path", "./path/to/models",
    "--dialect", "mysql",
  ]
}

env "drift" {
  dev = "docker://mysql/8/dev"
  # Variables defined and available with env:// prefix.
  gorm       = data.hcl_schema.gorm.url
  sequelize  = data.hcl_schema.sequelize.url
}
```

```shell
atlas schema diff \
  --env "drift" \
  --from "env://gorm" \
  --to "env://sequelize"
```

</TabItem>
<TabItem label="PostgreSQL" value="postgres">

```hcl
data "external_schema" "gorm" {
  program = [
    "go", "run", "-mod=mod",
    "gitee.com/damengde/atlas-provider-gorm",
    "load",
    "--path", "./path/to/models",
    "--dialect", "postgres",
  ]
}

data "external_schema" "sequelize" {
  program = [
    "npx",
    "@ariga/atlas-provider-sequelize",
    "load",
    "--path", "./path/to/models",
    "--dialect", "postgres",
  ]
}

env "drift" {
  dev = "docker://postgres/15/dev?search_path=public"
  # Variables defined and available with env:// prefix.
  gorm       = data.hcl_schema.gorm.url
  sequelize  = data.hcl_schema.sequelize.url
}
```

```shell
atlas schema diff \
  --env "drift" \
  --from "env://gorm" \
  --to "env://sequelize"
```

</TabItem>
<TabItem label="SQLite" value="sqlite">

```hcl
data "external_schema" "gorm" {
  program = [
    "go", "run", "-mod=mod",
    "gitee.com/damengde/atlas-provider-gorm",
    "load",
    "--path", "./path/to/models",
    "--dialect", "sqlite",
  ]
}

data "external_schema" "sequelize" {
  program = [
    "npx",
    "@ariga/atlas-provider-sequelize",
    "load",
    "--path", "./path/to/models",
    "--dialect", "sqlite",
  ]
}

env "drift" {
  dev = "sqlite://dev?mode=memory"
  # Variables defined and available with env:// prefix.
  gorm       = data.hcl_schema.gorm.url
  sequelize  = data.hcl_schema.sequelize.url
}
```

```shell
atlas schema diff \
  --env "drift" \
  --from "env://gorm" \
  --to "env://sequelize"
```

</TabItem>
<TabItem label="SQL Server" value="sqlserver">

```hcl
data "external_schema" "gorm" {
  program = [
    "go", "run", "-mod=mod",
    "gitee.com/damengde/atlas-provider-gorm",
    "load",
    "--path", "./path/to/models",
    "--dialect", "sqlserver",
  ]
}

data "external_schema" "sequelize" {
  program = [
    "npx",
    "@ariga/atlas-provider-sequelize",
    "load",
    "--path", "./path/to/models",
    "--dialect", "mssql",
  ]
}

env "drift" {
  dev = "docker://sqlserver/2022-latest"
  # Variables defined and available with env:// prefix.
  gorm       = data.hcl_schema.gorm.url
  sequelize  = data.hcl_schema.sequelize.url
}
```

```shell
atlas schema diff \
  --env "drift" \
  --from "env://gorm" \
  --to "env://sequelize"
```

</TabItem>
</Tabs>

### Indented SQL

The `schema diff` command outputs a list of SQL statements without indentation by default. If you would like to view
the SQL statements with indentation, use the `--format` flag. For example:

```shell {1}
# Indent SQL statements with 2 spaces.
atlas schema diff \
  --from "mysql://user:pass@localhost:3306/example" \
  --to "mysql://user:pass@remote:3306/example" \
  --format '{{ sql . "  " }}'
```

## Reference

[CLI Command Reference](/cli-reference#atlas-schema-diff)